package com.chauncy.DMS.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import util.List2Json1.List2Json1;

import com.chauncy.DMS.dao.ManagerDaoImplement;

public class StuAttCountServlet extends HttpServlet {
	
	private static ManagerDaoImplement daoImplement = new ManagerDaoImplement();
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String stuId = request.getParameter("stuId");
		String flag = request.getParameter("flag");
		String dNub = request.getParameter("dNub");
		/**
		 * 主要实现流程，前端ajax访问本servlet，携带参数
		 * stuId学号、 flag标识符、 dNub宿舍号
		 * 后台通过判断flag调用对应方法并response对应值
		 */ 
		if(flag.equals("qingjia")){
			response.getWriter().print(getQingJiaCount(stuId));
		}
		if(flag.equals("wangui")){
			System.out.println(getWanGuiCount(stuId));
			response.getWriter().print(getWanGuiCount(stuId));	
		}
		if(flag.equals("healthInfo")){
			response.getWriter().print(getHealthCount(dNub));
		}
		if(flag.equals("weigui")){
			response.getWriter().print(getWeiGuiCount(stuId));
		}
		if(flag.equals("allAtt")){
			List<Map<String, Object>> allAttInfoList = getAllAttInfoForStuId(stuId);
			System.out.println("stuId::"+stuId);
			System.out.println(allAttInfoList);
			JSONObject jsonObject = List2Json1.mapToJSON(allAttInfoList);
			response.getWriter().print(jsonObject);
			
		}
	}
	//获取请假次数
	private static Long getQingJiaCount(String stuId){
		String sql = "SELECT COUNT(Is_leave) AS 'qingjia' " +
				" FROM stu_att_info_table AS sait" +
				" WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(Att_date)" +
				" AND  Is_leave > 0 AND Stu_number = ? ;";
		Object[] param = {stuId};
		 List<Map<String, Object>> qingjiaList = daoImplement.query(sql, param);
		System.out.println("qingjia"+qingjiaList.get(0).get("qingjia"));
		return (Long) qingjiaList.get(0).get("qingjia");
	}
	//获取晚归次数
	private static Long getWanGuiCount(String stuId){
		String sql = "SELECT COUNT(Late_return_time) AS 'wangui' " +
				" FROM stu_att_info_table AS sait" +
				" WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(Att_date)" +
				" AND  Late_return_time > 0 AND Stu_number = ? ;";
		Object[] param = {stuId};
		 List<Map<String, Object>> wanguiList = daoImplement.query(sql, param);
		return (Long) wanguiList.get(0).get("wangui");
	}
	//获取未归次数
	private static Long getWeiGuiCount(String stuId){
		String sql = "SELECT COUNT(No_return) AS 'weigui' " +
				" FROM stu_att_info_table AS sait" +
				" WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(Att_date)" +
				" AND  No_return > 0 AND Stu_number = ? ;";
		Object[] param = {stuId};
		 List<Map<String, Object>> weiguiList = daoImplement.query(sql, param);
		System.out.println("weigui"+weiguiList.get(0).get("weigui"));
		return (Long) weiguiList.get(0).get("weigui");
	}
	//获取宿舍卫生扣分总分数
	private static Object getHealthCount(String dNub){
		String sql = "SELECT sum(health_info_table.The_Value) AS 'tValue' " +
				"FROM health_info_table" +
				" WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= health_info_table.Att_time" +
				" AND health_info_table.Dorm_number = ? ";
		Object[] param = {dNub};
		 List<Map<String, Object>> healthInfoList = daoImplement.query(sql, param);
		return  (Object) healthInfoList.get(0).get("tValue");
	}
	//根据学生学号获取考勤记录
	private static List<Map<String, Object>> getAllAttInfoForStuId(String stuId){
		String sql = "SELECT sait.Is_leave,sait.Time_for_leave," +
				" sait.Stay_out_late,sait.Late_return_time," +
				" sait.No_return,sait.remarks," +
				" DATE_FORMAT(Att_date,'%Y-%m-%d') AS Att_date" +
				" FROM stu_att_info_table AS sait " +
				"WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(Att_date)" +
				" AND Stu_number = ? " +
				" AND ( No_return > 0 OR Is_leave > 0 or Late_return_time > 0)";
		Object[] param = {stuId};
		return daoImplement.query(sql,param);
	}

}
